/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Connection;

import Models.Account;
import Models.Transactie;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Luuk
 */
public class Connector {
    
    private Connection con;
    
    public Connector(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/Javabank", "root", "");
            con.setAutoCommit(false);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException sqlex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, sqlex);
        }
        
    }
    
    public Account getAccount(int reknummer){
        try{
            Statement stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * FROM `account` WHERE Rekeningnummer = " + reknummer);
            Account account = new Account();
            while(rs.next()){
                account.setID(rs.getInt("ID"));
                account.setNaam(rs.getString("Naam"));
                account.setWoonplaats(rs.getString("Woonplaats"));
                account.setRekeningnummer(rs.getInt("Rekeningnummer"));
                account.setSaldo(rs.getInt("Saldo"));
                account.setLimiet(rs.getInt("Limiet"));
            }
            con.commit();
            return account;
        } catch (SQLException ex){
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public void alterAccount(Account account){
        try {
            Statement stmt = con.createStatement();

            stmt.executeUpdate("UPDATE `account` SET Naam = " + account.getNaam()
                    + " Woonplaats = " + account.getWoonplaats()
                    + " Saldo = " + account.getSaldo()
                    + " Limiet" + account.getLimiet()
                    + " WHERE Rekeningnummer = " + account.getRekeningnummer());
            con.commit();
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void saveAccount(Account account){
        try {
            Statement stmt = con.createStatement();

            stmt.executeUpdate("INSERT INTO `account` VALUES ("
                    + "NULL"
                    + ", '" + account.getNaam()
                    + "', '" + account.getWoonplaats()
                    + "', " + account.getRekeningnummer()
                    + ", " + account.getSaldo()
                    + ", " + account.getLimiet()
                    + ")");
            con.commit();
        } catch (SQLException ex) {
            Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public boolean transfer(float amount, int reknummerFrom, int reknummerTo){
        try {
            Statement stmt = con.createStatement();
            
            ResultSet rekFrom = stmt.executeQuery("SELECT * from `account` WHERE Rekeningnummer = " + reknummerFrom);
            float newSaldoFrom;
            float fromLimiet;
            if(rekFrom.next()){
                newSaldoFrom = rekFrom.getInt("Saldo") - amount;
                fromLimiet = rekFrom.getInt("Limiet");
                rekFrom.close();
            }else{
                return false;
            }
            
            ResultSet rekTo = stmt.executeQuery("SELECT * from `account` WHERE Rekeningnummer = " + reknummerTo);
            float newSaldoTo;
            if(rekTo.next()){
                 newSaldoTo = rekTo.getInt("Saldo") + amount;
                rekTo.close();
            }else{
                return false;
            }
            
            if(newSaldoFrom < fromLimiet){
                return false;
            }else{
                DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Calendar cal = Calendar.getInstance();
                stmt.executeUpdate("UPDATE `account` SET Saldo = " + newSaldoFrom + " WHERE Rekeningnummer = " + reknummerFrom);
                stmt.executeUpdate("UPDATE `account` SET Saldo = " + newSaldoTo + " WHERE Rekeningnummer = " + reknummerTo);
                stmt.executeUpdate("INSERT INTO `transactie` VALUES("
                        + "NULL"
                        + ", " + reknummerFrom
                        + ", " + reknummerTo
                        + ", " + amount
                        + ", '" + dateFormat.format(cal.getTime())
                        + "')");
            }
            con.commit();
            System.out.println("Committed");
            return true;
        } catch (SQLException ex) {
            try {
                con.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Connector.class.getName()).log(Level.SEVERE, null, ex1);
            }
            return false;
        }
    }
    
    public Transactie[] getTransactions(int reknummer, String date){
        try {
            Statement stmt = con.createStatement();
            
            ResultSet count = stmt.executeQuery("SELECT COUNT(*) from `transactie` WHERE (ontvanger = " + reknummer + " OR verzender = " + reknummer + ") AND Datum > '" + date + "'");
            count.next();
            Transactie[] transacties = new Transactie[count.getInt(1)];
            count. close();
            
            ResultSet transactions = stmt.executeQuery("SELECT * from `transactie` WHERE (ontvanger = " + reknummer + " OR verzender = " + reknummer + ") AND Datum > '" + date + "'");
            
            
            int i = 0;
            while(transactions.next()){
                Transactie ts = new Transactie();
                ts.setVerzender(transactions.getInt("Verzender"));
                ts.setOntvanger(transactions.getInt("Ontvanger"));
                ts.setBedrag(transactions.getInt("Bedrag"));
                ts.setDatum(transactions.getDate("Datum"));
                transacties[i] = ts;
                i++;
            }
            
            con.commit();
            return transacties;
        } catch (SQLException ex) {
            return null;
        }
    }
    
}
